CREATE FUNCTION CheckConditionalGraduation(@StudentId VARCHAR(5))
RETURNS BIT
AS
BEGIN
	DECLARE @totalCredits INT;
	DECLARE @gpa float;
	
	SELECT @totalCredits = sum(credits), @gpa = SUM(credits * mark) / SUM(credits)
		FROM course
			JOIN
			(SELECT * 
			FROM takes AS T
			WHERE
				T.ID=@studentId AND
				T.year >= ALL(SELECT year FROM takes WHERE ID=@studentId AND course_id = T.course_id) AND
				T.semester >= ALL (SELECT semester FROM takes WHERE ID=@studentId AND course_id = T.course_id)) AS TT
			ON course.course_id = TT.course_id
			JOIN mapping_mark ON TT.grade = mapping_mark.grade
	IF (@totalCredits < 50 OR @gpa < 1.0) RETURN 0
	RETURN 1;
END;